

define erp='erp_mli2.';
define outils='outils_mli2.';



/*
--select source,il_id1_cod,  il_id3_cod, il_id4_cod,c_condition_desc,a_fortype_cod, a_variable_cod,  a_forappel_cod, a_formatsais_cod,  a_action_desc from (
  SELECT &outils.stragg(source) source, il_arbre_id, il_fortype_cod, il_enreg_id, il_nivid_num, il_formule_cod, il_reutilisation_cod, il_utilisateur, il_etat_typ, il_ctrlappels_num, il_ctrlappelsdev_num, il_dev_typ, il_actdif_ok, il_id1_cod, il_id2_cod, il_id3_cod, il_id4_cod, il_id5_cod, il_id6_cod, il_id7_cod, il_id8_cod, il_horsse_ok, alc_arbre_id, alc_arbrepere_id, alc_enreg_id, alc_rang, alc_branche_typ, c_arbre_id, c_enreg_id, c_forappel_cod, c_condition_typ, c_condition_cod, c_condition_lib, c_condition_desc, c_faux_ok, c_renvoi_cod, aca_arbre_id, aca_arbrepere_id, aca_enreg_id, aca_rang, aca_branche_typ, a_arbre_id, a_enreg_id, a_fortype_cod, a_variable_cod, a_entite_cod, a_forappel_cod, a_formatsais_cod, a_action_typ, a_action_lib, a_action_desc, a_varinterm_cod
  FROM
      (SELECT DISTINCT 'uv' source , f.* FROM se_formule_v f

      UNION ALL

      SELECT 'se' source ,ael.arbre_id ael_arbre_id, ael.arbrepere_id ael_arbrepere_id, ael.enreg_id ael_enreg_id, ael.rang ael_rang, ael.branche_typ ael_branche_typ,
            il.arbre_id il_arbre_id, il.fortype_cod il_fortype_cod, il.enreg_id il_enreg_id, il.nivid_num il_nivid_num, il.formule_cod il_formule_cod, il.reutilisation_cod il_reutilisation_cod, il.utilisateur il_utilisateur, il.etat_typ il_etat_typ, il.ctrlappels_num il_ctrlappels_num, il.ctrlappelsdev_num il_ctrlappelsdev_num, il.dev_typ il_dev_typ, il.actdif_ok il_actdif_ok, il.id1_cod il_id1_cod, il.id2_cod il_id2_cod, il.id3_cod il_id3_cod, il.id4_cod il_id4_cod, il.id5_cod il_id5_cod, il.id6_cod il_id6_cod, il.id7_cod il_id7_cod, il.id8_cod il_id8_cod, il.horsse_ok il_horsse_ok,
            alc.arbre_id alc_arbre_id, alc.arbrepere_id alc_arbrepere_id, alc.enreg_id alc_enreg_id, alc.rang alc_rang, alc.branche_typ alc_branche_typ,
            c.arbre_id c_arbre_id, c.enreg_id c_enreg_id, c.forappel_cod c_forappel_cod, c.condition_typ c_condition_typ, c.condition_cod c_condition_cod, c.condition_lib c_condition_lib, c.condition_desc c_condition_desc,c.condition_desc c_condition_uv, c.faux_ok c_faux_ok, c.renvoi_cod c_renvoi_cod,
            aca.arbre_id aca_arbre_id, aca.arbrepere_id aca_arbrepere_id, aca.enreg_id aca_enreg_id, aca.rang aca_rang, aca.branche_typ aca_branche_typ,
            a.arbre_id a_arbre_id, a.enreg_id a_enreg_id, a.fortype_cod a_fortype_cod, a.variable_cod a_variable_cod, a.entite_cod a_entite_cod, a.forappel_cod a_forappel_cod, a.formatsais_cod a_formatsais_cod, a.action_typ a_action_typ, a.action_lib a_action_lib, a.action_desc a_action_desc,a.action_desc a_action_uv, a.varinterm_cod a_varinterm_cod
      FROM erp_mli2.for_arbre ael,
          erp_mli2.for_identifiant il,     erp_mli2.for_arbre alc,
          erp_mli2.for_condition c,
          erp_mli2.for_arbre aca,          erp_mli2.for_action a
      WHERE ael.arbre_id= il.arbre_id
        AND alc.arbrepere_id=il.arbre_id         AND alc.arbre_id=c.arbre_id
        AND aca.arbrepere_id=c.arbre_id          AND aca.arbre_id=a.arbre_id
        AND il.FORTYPE_COD='ATTVRT'

        )

  GROUP BY  il_arbre_id, il_fortype_cod, il_enreg_id, il_nivid_num, il_formule_cod, il_reutilisation_cod, il_utilisateur, il_etat_typ, il_ctrlappels_num, il_ctrlappelsdev_num, il_dev_typ, il_actdif_ok, il_id1_cod, il_id2_cod, il_id3_cod, il_id4_cod, il_id5_cod, il_id6_cod, il_id7_cod, il_id8_cod, il_horsse_ok, alc_arbre_id, alc_arbrepere_id, alc_enreg_id, alc_rang, alc_branche_typ, c_arbre_id, c_enreg_id, c_forappel_cod, c_condition_typ, c_condition_cod, c_condition_lib, c_condition_desc, c_faux_ok, c_renvoi_cod, aca_arbre_id, aca_arbrepere_id, aca_enreg_id, aca_rang, aca_branche_typ, a_arbre_id, a_enreg_id, a_fortype_cod, a_variable_cod, a_entite_cod, a_forappel_cod, a_formatsais_cod, a_action_typ, a_action_lib, a_action_desc, a_varinterm_cod
  HAVING Count(1)!=2--) r
ORDER BY IL_ID1_COD,IL_ID3_COD,IL_ID4_COD,SOURCE,C_CONDITION_DESC,ACA_RANG    ;
  */
/*
PROMPT CREATE INDEX se_formule_v_01
CREATE INDEX se_formule_v_01
  ON se_formule_V (
    il_arbre_id
 )
  NOLOGGING
;
PROMPT CREATE INDEX se_formule_V_02
CREATE INDEX se_formule_V_02
  ON se_formule_V (
    ie_id2_cod,ie_id3_cod,ie_id4_cod
 )
  NOLOGGING
;
PROMPT CREATE INDEX se_formule_V_03
CREATE INDEX se_formule_V_03
  ON se_formule_V (
    il_id2_cod,il_id3_cod,il_id4_cod,il_id5_cod
 )
  NOLOGGING
;


*/
 /*eclatement des actions pour avoir une action par ligne*/
DROP TABLE se_formule_V_eclataction ;

CREATE TABLE se_formule_V_eclataction AS (
    SELECT id,RANGMULTI2,p.x rangeclat,
          calcul, SubStr(calcul,InStr('@'||calcul,'@',1,x+1),InStr('@'||calcul||'@','@',1,x+2)-InStr('@'||calcul,'@',1,x+1)-1) calculeclat
    FROM
    (SELECT id,RANGMULTI2,REGEXP_REPLACE(calcul,'(;{1})( ){0,1}(RES|TYPEZONE|CNTR|ER)','@\3' ) calcul
      FROM uv_ptablesprogv_calcul /*WHERE calcul LIKE '%0<>100%'*/) r, &outils.pivot p
    WHERE p.x<=(Length(calcul)-Length(REPLACE(calcul,'@','')))
    --ORDER BY id,RANGMULTI2,x
)  ;





 DELETE FROM se_formule_V;

 INSERT INTO se_formule_V
 (SELECT DISTINCT 0 ael_arbre_id,null ael_arbrepere_id,0 ael_enreg_id,2 ael_rang,'I' ael_branche_typ,
         0 il_arbre_id,'ATTVRT' il_fortype_cod,0 il_enreg_id,1 il_nivid_num,null il_formule_cod,null il_reutilisation_cod,null il_utilisateur,null il_etat_typ,null il_ctrlappels_num,null il_ctrlappelsdev_num,'C' il_dev_typ,'N' il_actdif_ok,
            fo.codform il_id1_cod,null il_id2_cod,fo.covar il_id3_cod,fo.attvar il_id4_cod,null il_id5_cod,null il_id6_cod,null il_id7_cod,null il_id8_cod,'N' il_horsse_ok,
         0 alc_arbre_id,0 alc_arbrepere_id,0 alc_enreg_id,c.rangmulti2+1 alc_rang,'C' alc_branche_typ,
         0 c_arbre_id,0 c_enreg_id,null c_forappel_cod,Decode(c.condition,'*',1,2) c_condition_typ,null c_condition_cod,null c_condition_lib,
            Decode(c.condition,'*','ACTION SANS CONDITION',c.condition) c_condition_desc,c.condition c_condition_uv ,
         'N' c_faux_ok,null c_renvoi_cod,
         0 aca_arbre_id,0 aca_arbrepere_id,0 aca_enreg_id,val.RANGECLAT aca_rang,'A' aca_branche_typ,
         0 a_arbre_id,0 a_enreg_id,'ATTVRT' a_fortype_cod,null a_variable_cod,null a_entite_cod,ZONE a_forappel_cod,'TXTLONG' a_formatsais_cod,'2' a_action_typ,null a_action_lib,
         TYPFORM ||'_'||val.CALCULeclat a_action_desc,TYPFORM ||'_'||val.CALCULeclat a_action_uv,null a_varinterm_cod
  --SELECT *
  FROM uv_ptablesprogv fo,uv_ptablesprogv_condition c,se_formule_V_eclataction val
  WHERE c.id=val.id AND c.RANGMULTI2=val.RANGMULTI2
    AND fo.id=c.id
   -- AND fo.covar='BBD' AND fo.attvar='1'


 ) ;

/*formule pas traduisible donc resaisie manuellement*/
DELETE FROM se_formule_V WHERE  IL_ID3_COD ='LON' OR  IL_ID3_COD ='TAI' ;


--SELECT DISTINCT il_id3_cod,il_id4_cod, c_condition_desc,c_condition_uv FROM se_formule_V  ORDER BY 3
--SELECT DISTINCT il_id3_cod,il_id4_cod, c_condition_desc,c_condition_uv,a_action_desc,a_action_uv FROM se_formule_V where IL_ID3_COD ='RMD' ORDER BY 3
--SELECT DISTINCT il_id3_cod,il_id4_cod,A_FORMATSAIS_COD,A_FORAPPEL_COD,A_VARIABLE_COD,a_action_desc,a_action_uv FROM se_formule_V ORDER BY 6


/* traduction des conditions*/
UPDATE se_formule_V SET c_condition_desc=REGEXP_REPLACE(c_condition_desc,'(SFA)( ){0,}(=|#)','ART.COMPUNFAB("UNFAB_COD")\3');
UPDATE se_formule_V SET c_condition_desc=REGEXP_REPLACE(c_condition_desc,'(SFA)(\"){1}(={0,1})','ART.COMPUNFAB("UNFAB_COD")=\2');

UPDATE se_formule_V SET c_condition_desc=REGEXP_REPLACE(c_condition_desc,'(ORIGINE)( ){0,}=( ){0,}"PXT"','1=1/*ORIGINE="PXT"*/') ;
UPDATE se_formule_V SET c_condition_desc=REPLACE(c_condition_desc,'=>','>=') ;
UPDATE se_formule_V SET c_condition_desc=REPLACE(c_condition_desc,'=<','<=') ;
UPDATE se_formule_V SET c_condition_desc=REPLACE(c_condition_desc,'#','<>') ;
UPDATE se_formule_V SET c_condition_desc=REPLACE(c_condition_desc,'NOT(','vide(') ;
UPDATE se_formule_V SET c_condition_desc=REGEXP_REPLACE(c_condition_desc,'vide\((.{3})\)','vide(attvrt("","\1","1"))') ;
UPDATE se_formule_V SET c_condition_desc=REGEXP_REPLACE(c_condition_desc,'^([A-Z]{3})$','attvrt("","\1","1")!=""') ;


UPDATE se_formule_V SET c_condition_desc=REGEXP_REPLACE(c_condition_desc,'(.{3})\<([[:digit:]]{1}),([[:digit:]]{1}),([[:digit:]]{1})\>','attvrt("","\1","\4")')  WHERE c_condition_desc LIKE '%<%>%' ;
UPDATE se_formule_V SET c_condition_desc=REGEXP_REPLACE(c_condition_desc,'(.{3})\<([[:digit:]]{1})\>','attvrt("","\1","\2")')  WHERE c_condition_desc LIKE '%<%>%' ;

UPDATE se_formule_V SET c_condition_desc=REGEXP_REPLACE(c_condition_desc,'(.{1,}) AND (.{1,})','(\1) AND (\2)') WHERE c_condition_desc LIKE '% AND %' ;
UPDATE se_formule_V SET c_condition_desc=REGEXP_REPLACE(c_condition_desc,'(.{1,}) OR (.{1,})','(\1) OR (\2)')  WHERE c_condition_desc LIKE '% OR %' ;

 /*supprime les .. inutile avant les valeur entre "" */
UPDATE se_formule_V SET c_condition_desc=REGEXP_REPLACE(c_condition_desc,'(\")(\.{1,})(.{1,})(\")','"\3"');
 /*supprime les .. inutile apr�s les valeur entre "" */
UPDATE se_formule_V SET c_condition_desc=REGEXP_REPLACE(c_condition_desc,'(\")(.{1,})(\.{1,})(\")','"\2"');

UPDATE se_formule_V SET c_condition_desc=REPLACE(c_condition_desc,'PR(28)','ART.COMP("ART_FAM_COD")') ;
UPDATE se_formule_V SET c_condition_desc=REPLACE(c_condition_desc,'PR.SOURCE<>"0"','ART.COMP("SOURCE_TYP")!="A"') ;
UPDATE se_formule_V SET c_condition_desc=REPLACE(c_condition_desc,'CL.CLE','[CLI.CLIENT.CLIENT_COD]') ;
UPDATE se_formule_V SET c_condition_desc=REPLACE(c_condition_desc,'RES=','attvrt("","'||IL_ID3_COD||'","'||IL_ID4_COD||'")=') ;
UPDATE se_formule_V SET c_condition_desc=REPLACE(c_condition_desc,'=RES','=attvrt("","'||IL_ID3_COD||'","'||IL_ID4_COD||'")') ;

UPDATE se_formule_V SET c_condition_desc=REPLACE(c_condition_desc,'RES<>','attvrt("","'||IL_ID3_COD||'","'||IL_ID4_COD||'")!=') ;
UPDATE se_formule_V SET c_condition_desc=REPLACE(c_condition_desc,'RES>','attvrt("","'||IL_ID3_COD||'","'||IL_ID4_COD||'")>') ;
UPDATE se_formule_V SET c_condition_desc=REPLACE(c_condition_desc,'RES<','attvrt("","'||IL_ID3_COD||'","'||IL_ID4_COD||'")<') ;


UPDATE se_formule_V SET c_condition_desc=REPLACE(c_condition_desc,'MC(4)','[CLI_CLIMQ.CLIHIER_COD]') ;
UPDATE se_formule_V SET c_condition_desc=REPLACE(c_condition_desc,'MO(12)','ART.REFE("COLLECTION_COD")') ;
UPDATE se_formule_V SET c_condition_desc=REPLACE(c_condition_desc,'LC(2)','[CLF_PIECELIG.REFE_COD]') ;
UPDATE se_formule_V SET c_condition_desc=REGEXP_REPLACE(c_condition_desc,'\(([A-Z]{3}=)','(attvrt("","\1","1")=') ;
UPDATE se_formule_V SET c_condition_desc=REGEXP_REPLACE(c_condition_desc,'\[CLI_CLIMQ\.CLIHIER_COD\]=\"([0-9]{0,3})\"','[CLI_CLIMQ.CLIHIER_COD]="00\1"') ;


UPDATE se_formule_V SET a_action_desc='"A"',A_FORMATSAIS_COD='TXTCOURT',A_FORAPPEL_COD='AVVAR',A_VARIABLE_COD='VALCTRLTYP'
                    WHERE A_ACTION_DESC ='IMP_TYPEZONE="S"' or A_ACTION_DESC ='IMP_TYPEZONE=""' ;
UPDATE se_formule_V SET a_action_desc='"I"',A_FORMATSAIS_COD='TXTCOURT',A_FORAPPEL_COD='AVVAR',A_VARIABLE_COD='VALCTRLTYP'
                    WHERE A_ACTION_DESC ='IMP_TYPEZONE="N"' ;

UPDATE se_formule_V SET a_action_desc=REPLACE(REPLACE(REPLACE(A_ACTION_DESC,'IMP_CNTR=',''),'"',''),';','$S$')||'$S$',
                        A_FORMATSAIS_COD='LISTE',A_FORAPPEL_COD='AVVAR',A_VARIABLE_COD='VALCTRL'
                    WHERE A_ACTION_DESC LIKE 'IMP_CNTR=%' ;
UPDATE se_formule_V SET a_action_desc=REPLACE(REPLACE(REPLACE(A_ACTION_DESC,'IMP_CNTR :=',''),'"',''),';','$S$')||'$S$',
                        A_FORMATSAIS_COD='LISTE',A_FORAPPEL_COD='AVVAR',A_VARIABLE_COD='VALCTRL'
                    WHERE A_ACTION_DESC LIKE 'IMP_CNTR :=%' ;

UPDATE se_formule_V SET A_FORMATSAIS_COD='TXTLONG' WHERE A_VARIABLE_COD = 'VALCTRL' AND A_ACTION_DESC NOT LIKE '%$%' ;

UPDATE se_formule_V SET a_action_desc=REPLACE(A_ACTION_DESC,'CTL_ER=','0$S$') ,
                        A_FORMATSAIS_COD='MESS',A_FORAPPEL_COD='AVVAR',A_VARIABLE_COD='CTRLSAIS'
                    WHERE A_ACTION_DESC LIKE 'CTL_ER=%' ;
UPDATE se_formule_V SET a_action_desc=REPLACE(A_ACTION_DESC,'CTL_RES=','0$S$') ,
                        A_FORMATSAIS_COD='MESS',A_FORAPPEL_COD='AVVAR',A_VARIABLE_COD='CTRLSAIS'
                    WHERE A_ACTION_DESC LIKE 'CTL_RES=%' ;

UPDATE se_formule_V SET a_action_desc=REPLACE(A_ACTION_DESC,'IMP_RES=','') ,
                        A_FORMATSAIS_COD='TXTLONG',A_FORAPPEL_COD='AVVAR',A_VARIABLE_COD='VALEUR'
                    WHERE A_ACTION_DESC LIKE 'IMP_RES=%' ;
UPDATE se_formule_V SET a_action_desc=REPLACE(A_ACTION_DESC,'IMP_RES = ','') ,
                        A_FORMATSAIS_COD='TXTLONG',A_FORAPPEL_COD='AVVAR',A_VARIABLE_COD='VALEUR'
                    WHERE A_ACTION_DESC LIKE 'IMP_RES = %' ;
 /*supprime les .. inutile avant les valeur entre "" */
UPDATE se_formule_V SET a_action_desc=REGEXP_REPLACE(a_action_desc,'(\")(\.{1,})(.{1,})(\")','"\3"');
 /*supprime les .. inutile apr�s les valeur entre "" */
UPDATE se_formule_V SET a_action_desc=REGEXP_REPLACE(a_action_desc,'(\")(.{1,})(\.{1,})(\")','"\2"');


UPDATE se_formule_V SET a_action_desc=REGEXP_REPLACE(a_action_desc,'(.{3})\<([[:digit:]]{1}),([[:digit:]]{1}),([[:digit:]]{1})\>','attvrt("","\1","\4")')  WHERE a_action_desc LIKE '%<%>%' ;
UPDATE se_formule_V SET a_action_desc=REGEXP_REPLACE(a_action_desc,'(.{3})\<([[:digit:]]{1})\>','attvrt("","\1","\2")')  WHERE a_action_desc LIKE '%<%>%' ;
UPDATE se_formule_V SET a_action_desc=REPLACE(a_action_desc,'RES$S$','attvrt("","'||IL_ID3_COD||'","'||IL_ID4_COD||'")') ;
UPDATE se_formule_V SET a_action_desc=REPLACE(a_action_desc,'MO(12)','ART.REFE("COLLECTION_COD")') ;
UPDATE se_formule_V SET a_action_desc=REPLACE(a_action_desc,'LEN(attvrt("","MNO","1")); FOR K1 = 37 TO 1 STEP -1;IF MNO[K1,1]="." THEN RES=-1;NEXT K1','longueur( replace( ".", "", attvrt("","MNO","1")))') ;

UPDATE se_formule_V SET A_FORMATSAIS_COD  ='TXTLONG'  WHERE A_VARIABLE_COD='VALCTRL' AND A_ACTION_DESC NOT LIKE '%$S$%'  ;

update se_formule_V SET il_id1_cod ='9999' WHERE il_id1_cod ='GENE' and il_FORTYPE_COD='ATTVRT'   ;

/* attribution des id au for_arbre de l'identifiant lignes (composants)*/
 UPDATE se_formule_V f SET (il_arbre_id,ael_arbre_id,alc_arbrepere_id,il_enreg_id,IL_CTRLAPPELSDEV_NUM) =

 (SELECT i.arbre_id,i.arbre_id,i.arbre_id,i.enreg_id,i.CTRLAPPELSDEV_NUM from erp_mli2.for_identifiant i ,erp_mli2.for_arbre il
  WHERE il.arbre_id=i.arbre_id  AND i.fortype_cod='ATTVRT' and i.fortype_cod=il_fortype_cod AND i.id1_cod =il_id1_cod  /*AND i.id2_cod=il_id2_cod*/ AND i.id3_cod=il_id3_cod AND i.id4_cod=il_id4_cod /* ANDi.id5_cod=il_id5_cod AND i.id6_cod|| ' '=il_id6_cod|| ' ' AND i.id7_cod|| ' '=il_id7_cod|| ' ' AND i.id8_cod|| ' '=il_id8_cod|| ' ' */
    )
 WHERE EXISTS  (SELECT i.arbre_id from erp_mli2.for_identifiant i ,erp_mli2.for_arbre il
  WHERE il.arbre_id=i.arbre_id  AND i.fortype_cod='ATTVRT' AND i.fortype_cod=il_fortype_cod AND i.id1_cod =il_id1_cod  /*AND i.id2_cod=il_id2_cod*/ AND i.id3_cod=il_id3_cod AND i.id4_cod=il_id4_cod /* AND i.id5_cod=il_id5_cod AND i.id6_cod|| ' '=il_id6_cod|| ' ' AND i.id7_cod|| ' '=il_id7_cod|| ' ' AND i.id8_cod|| ' '=il_id8_cod|| ' ' */
    );


  /* attribution de nouvel id aux identifiant lignes (composants) non renseign�s*/
  DROP TABLE se_formule_V_il;
  CREATE TABLE se_formule_V_il as
      (SELECT outils_mli2.for_arbre.NEXTVAL arbre_id
            ,outils_mli2.enreg_id.NEXTVAL enreg_id1
            ,outils_mli2.enreg_id.NEXTVAL enreg_id2,ss.*
      from (SELECT DISTINCT il_fortype_cod,il_id1_cod ,il_id2_cod,il_id3_cod,il_id4_cod,il_id5_cod,il_id6_cod,il_id7_cod,il_id8_cod
            FROM se_formule_V  WHERE il_arbre_id =0) ss );
  UPDATE se_formule_V_il SET enreg_id2= outils_mli2.enreg_id.NEXTVAL;


PROMPT CREATE INDEX se_formule_V_il_01
CREATE INDEX se_formule_V_il_01
  ON se_formule_V_il (
    arbre_id
 )
  NOLOGGING
;
PROMPT CREATE INDEX se_formule_V_il_02
CREATE INDEX se_formule_V_il_02
  ON se_formule_V_il (
    il_id1_cod,il_id3_cod,il_id4_cod
 )
  NOLOGGING
;



  UPDATE se_formule_V i SET (il_arbre_id,ael_arbre_id,alc_arbrepere_id,il_enreg_id,ael_enreg_id) =
    (SELECT arbre_id,arbre_id,arbre_id,enreg_id1,enreg_id2 FROM se_formule_V_il s
    WHERE i.il_fortype_cod=s.il_fortype_cod AND i.il_id1_cod  =s.il_id1_cod  /* AND i.il_id2_cod=s.il_id2_cod*/ AND i.il_id3_cod=s.il_id3_cod AND i.il_id4_cod=s.il_id4_cod/* AND i.il_id5_cod=s.il_id5_cod AND i.il_id6_cod|| ' '=s.il_id6_cod|| ' ' AND i.il_id7_cod|| ' '=s.il_id7_cod|| ' ' AND i.il_id8_cod|| ' '=s.il_id8_cod|| ' '*/
    )
  WHERE i.il_arbre_id=0
    AND EXISTS     (SELECT 1 FROM se_formule_V_il s
    WHERE i.il_fortype_cod=s.il_fortype_cod AND i.il_id1_cod  =s.il_id1_cod   /*AND i.il_id2_cod=s.il_id2_cod*/ AND i.il_id3_cod=s.il_id3_cod AND i.il_id4_cod=s.il_id4_cod/* AND i.il_id5_cod=s.il_id5_cod AND i.il_id6_cod|| ' '=s.il_id6_cod|| ' ' AND i.il_id7_cod|| ' '=s.il_id7_cod|| ' ' AND i.il_id8_cod|| ' '=s.il_id8_cod|| ' ' */
    )
;


  UPDATE se_formule_V l SET ael_enreg_id =(SELECT enreg_id from erp_mli2.for_arbre a WHERE a.arbre_id=l.ael_arbre_id)
  WHERE EXISTS (SELECT enreg_id from erp_mli2.for_arbre a WHERE a.arbre_id=l.ael_arbre_id) ;

  UPDATE se_formule_V l SET il_formule_cod =(SELECT formule_cod FROM erp_mli2.for_identifiant i WHERE i.enreg_id=l.il_enreg_id)
  WHERE exists (SELECT formule_cod FROM erp_mli2.for_identifiant i WHERE i.enreg_id=l.il_enreg_id) ;

/* attribution des id au for_arbre des conditions des lignes (composants)*/
 UPDATE se_formule_V l SET (alc_arbre_id,c_arbre_id,aca_arbrepere_id) =
 (SELECT Min(c.arbre_id),Min(c.arbre_id),Min(c.arbre_id) FROM erp_mli2.for_condition c, erp_mli2.for_arbre alc WHERE alc.arbre_id=c.arbre_id AND alc.arbrepere_id=l.alc_arbrepere_id AND c.condition_desc=l.c_condition_desc)
 WHERE EXISTS (SELECT c.arbre_id FROM erp_mli2.for_condition c, erp_mli2.for_arbre alc WHERE alc.arbre_id=c.arbre_id AND alc.arbrepere_id=l.alc_arbrepere_id AND c.condition_desc=l.c_condition_desc) ;

 UPDATE se_formule_V l SET (alc_enreg_id,ALC_RANG) =(SELECT enreg_id,rang from erp_mli2.for_arbre a WHERE a.arbre_id=l.alc_arbre_id)
 WHERE EXISTS (SELECT enreg_id from erp_mli2.for_arbre a WHERE a.arbre_id=l.alc_arbre_id)  ;

 UPDATE se_formule_V l SET c_enreg_id =(SELECT enreg_id from erp_mli2.for_condition a WHERE a.arbre_id=l.c_arbre_id)
 WHERE EXISTS (SELECT enreg_id from erp_mli2.for_condition a WHERE a.arbre_id=l.c_arbre_id) ;

 UPDATE se_formule_V l SET ALC_RANG =Nvl((SELECT rang from erp_mli2.for_arbre a WHERE a.arbre_id=l.c_arbre_id),alc_rang);

  /* attribution de nouvel id aux condition des lignes (composants) non renseign�s*/
  DROP TABLE se_formule_V_c;
  CREATE TABLE se_formule_V_c as
      (SELECT outils_mli2.for_arbre.NEXTVAL arbre_id
            ,outils_mli2.enreg_id.NEXTVAL enreg_id1
            ,outils_mli2.enreg_id.NEXTVAL enreg_id2,ss.*
      from (SELECT DISTINCT c_condition_desc,alc_arbrepere_id,il_id1_cod, il_id3_cod, il_id4_cod
            FROM se_formule_V  WHERE c_arbre_id =0) ss );
  UPDATE se_formule_V_c SET enreg_id2= outils_mli2.enreg_id.NEXTVAL;


PROMPT CREATE INDEX se_formule_V_il_01
CREATE INDEX se_formule_V_c_01
  ON se_formule_V_c (
    arbre_id
 )
  NOLOGGING
;
PROMPT CREATE INDEX se_formule_V_il_02
CREATE INDEX se_formule_V_c_02
  ON se_formule_V_c (
    il_id1_cod,il_id3_cod,il_id4_cod
 )
  NOLOGGING
;



  UPDATE se_formule_V i SET (c_arbre_id,alc_arbre_id,aca_arbrepere_id,c_enreg_id,alc_enreg_id) =
    (SELECT arbre_id,arbre_id,arbre_id,enreg_id1,enreg_id2 FROM se_formule_V_c s
    WHERE s.c_condition_desc|| ' ' =i.c_condition_desc|| ' ' AND s.alc_arbrepere_id = i.alc_arbrepere_id
      AND i.il_id1_cod=s.il_id1_cod AND i.il_id3_cod=s.il_id3_cod AND i.il_id4_cod=s.il_id4_cod
    )
  WHERE i.c_arbre_id=0   ;


/* attribution des id au for_arbre des actions des lignes (composants)*/
 UPDATE se_formule_V l SET (aca_arbre_id,a_arbre_id) =
 (SELECT Min(a.arbre_id),Min(a.arbre_id) FROM erp_mli2.for_action a, erp_mli2.for_arbre aca WHERE aca.arbre_id=a.arbre_id AND aca.arbrepere_id=l.aca_arbrepere_id AND a.action_desc||' '=l.a_action_desc||' ' AND a.varinterm_cod||' '=l.a_varinterm_cod||' ' AND a.variable_cod||' '=l.a_variable_cod||' ' AND a.formatsais_cod||' '=l.a_formatsais_cod||' ' AND a.action_typ||' '=l.a_action_typ||' ')
 WHERE EXISTS  (SELECT (a.arbre_id) FROM erp_mli2.for_action a, erp_mli2.for_arbre aca WHERE aca.arbre_id=a.arbre_id AND aca.arbrepere_id=l.aca_arbrepere_id AND a.action_desc||' '=l.a_action_desc||' ' AND a.varinterm_cod||' '=l.a_varinterm_cod||' ' AND a.variable_cod||' '=l.a_variable_cod||' ' AND a.formatsais_cod||' '=l.a_formatsais_cod||' ' AND a.action_typ||' '=l.a_action_typ||' ')  ;

  UPDATE se_formule_V l SET (aca_enreg_id,aca_rang) =(SELECT enreg_id,rang from erp_mli2.for_arbre a WHERE a.arbre_id=l.aca_arbre_id)
 WHERE EXISTS (SELECT enreg_id from erp_mli2.for_arbre a WHERE a.arbre_id=l.aca_arbre_id) ;

 UPDATE se_formule_V l SET a_enreg_id =(SELECT enreg_id from erp_mli2.for_action a WHERE a.arbre_id=l.a_arbre_id)
 WHERE EXISTS (SELECT enreg_id from erp_mli2.for_action a WHERE a.arbre_id=l.a_arbre_id) ;


  /* attribution de nouvel id aux actions des lignes (composants) non renseign�s*/
  DROP TABLE se_formule_V_a;
  CREATE TABLE se_formule_V_a as
      (SELECT outils_mli2.for_arbre.NEXTVAL arbre_id
            ,outils_mli2.enreg_id.NEXTVAL enreg_id1
            ,outils_mli2.enreg_id.NEXTVAL enreg_id2,ss.*
      from (SELECT DISTINCT aca_arbrepere_id,a_action_desc,a_varinterm_cod,a_variable_cod,a_formatsais_cod,a_action_typ
            FROM se_formule_V  WHERE a_arbre_id =0) ss );
  UPDATE se_formule_V_a SET enreg_id2= outils_mli2.enreg_id.NEXTVAL;

PROMPT CREATE INDEX se_formule_V_a_01
CREATE INDEX se_formule_V_a_01
  ON se_formule_V_a (
    arbre_id
 )
  NOLOGGING
;
PROMPT CREATE INDEX se_formule_V_a_02
CREATE INDEX se_formule_V_a_02
  ON se_formule_V_a (
    aca_arbrepere_id
 )
  NOLOGGING
;


  UPDATE se_formule_V i SET (a_arbre_id,aca_arbre_id,a_enreg_id,aca_enreg_id) =
    (SELECT arbre_id,arbre_id,enreg_id1,enreg_id2 FROM se_formule_V_a s
    WHERE s.aca_arbrepere_id=i.aca_arbrepere_id AND s.a_action_desc|| ' ' = i.a_action_desc|| ' ' AND s.a_varinterm_cod|| ' ' =i.a_varinterm_cod|| ' ' AND s.a_variable_cod|| ' ' = i.a_variable_cod|| ' ' AND s.a_formatsais_cod|| ' ' = i.a_formatsais_cod|| ' ' AND s.a_action_typ|| ' ' = i.a_action_typ|| ' '
    )
  WHERE i.a_arbre_id=0   ;


 DROP TABLE se_formule_V_temp_rang ;
 CREATE TABLE  se_formule_V_temp_rang AS
      (SELECT il_id3_cod,il_id4_cod,alc_rang,c_condition_desc,aca_rang,a_action_desc ,alc_enreg_id ,aca_enreg_id,
            dense_rank() OVER (PARTITION BY IL_ID3_COD,IL_ID4_COD ORDER BY IL_ID3_COD,IL_ID4_COD,ALC_RANG,C_CONDITION_DESC desc ) new_alcrang
      FROM  se_formule_V );

 UPDATE se_formule_V v SET ALC_RANG = (SELECT  Max(NEW_ALCRANG) FROM se_formule_V_temp_rang t WHERE t.ALC_ENREG_ID=v.ALC_ENREG_ID AND t.aca_enreg_id=v.aca_enreg_id);

 DROP TABLE se_formule_V_temp_rang ;
 CREATE TABLE  se_formule_V_temp_rang AS
      (SELECT il_id3_cod,il_id4_cod,alc_rang,c_condition_desc,aca_rang,a_action_desc ,alc_enreg_id ,aca_enreg_id,
            dense_rank() OVER (PARTITION BY IL_ID3_COD,IL_ID4_COD,ALC_RANG ORDER BY IL_ID3_COD,IL_ID4_COD,ALC_RANG,C_CONDITION_DESC desc,ACA_RANG ) new_aca_rang
      FROM  se_formule_V  );

 UPDATE se_formule_V v SET ACA_RANG = (SELECT  Max(NEW_ACA_RANG) FROM se_formule_V_temp_rang t WHERE t.ALC_ENREG_ID=v.ALC_ENREG_ID AND t.aca_enreg_id=v.aca_enreg_id);


INSERT INTO se_formule_V  (
     SELECT ael.arbre_id, ael.arbrepere_id, ael.enreg_id, ael.rang, ael.branche_typ,
      il.arbre_id, il.fortype_cod, il.enreg_id, il.nivid_num, il.formule_cod, il.reutilisation_cod, il.utilisateur, il.etat_typ, il.ctrlappels_num, il.ctrlappelsdev_num, il.dev_typ, il.actdif_ok, il.id1_cod, il.id2_cod, il.id3_cod, il.id4_cod, il.id5_cod, il.id6_cod, il.id7_cod, il.id8_cod,il.horsse_ok,
      alc.arbre_id, alc.arbrepere_id, alc.enreg_id, alc.rang, alc.branche_typ,
      c.arbre_id, c.enreg_id, c.forappel_cod, c.condition_typ, c.condition_cod, c.condition_lib, c.condition_desc, NULL condition_uv, c.faux_ok, c.renvoi_cod,
      aca.arbre_id, aca.arbrepere_id, aca.enreg_id, aca.rang, aca.branche_typ,
      a.arbre_id, a.enreg_id, a.fortype_cod, a.variable_cod, a.entite_cod, a.forappel_cod, a.formatsais_cod, a.action_typ, a.action_lib, a.action_desc, NULL action_uv, a.varinterm_cod
    FROM &erp.for_arbre ael ,&erp.for_identifiant il ,&erp.for_arbre alc, &erp.for_condition c, &erp.for_arbre aca, &erp.for_action a
    WHERE ael.arbre_id=alc.arbrepere_id
      AND alc.arbre_id=aca.arbrepere_id
      AND il.arbre_id=ael.arbre_id
      AND c.arbre_id=alc.arbre_id
      AND a.arbre_id=aca.arbre_id
      AND il.fortype_cod='ATTVRT'
      AND IL.ID3_COD IN ('LON','TAI')
)    ;


commit;




/* FORMULE Vrt */
 DELETE FROM se_for_arbre ;

 INSERT INTO se_for_arbre
 (SELECT DISTINCT ael_arbre_id arbre_id,
         null arbrepere_id,
         ael_enreg_id enreg_id,
         ael_rang rang,
         ael_branche_typ branche_typ
    FROM se_formule_V) ;

 INSERT INTO se_for_arbre
 (SELECT alc_arbre_id arbre_id,
         alc_arbrepere_id arbrepere_id,
         alc_enreg_id enreg_id,
         Max(alc_rang) rang,
         alc_branche_typ branche_typ
    FROM se_formule_V
    GROUP  BY alc_arbre_id,alc_arbrepere_id,alc_enreg_id,alc_branche_typ) ;

 INSERT INTO se_for_arbre
 (SELECT DISTINCT aca_arbre_id arbre_id,
         aca_arbrepere_id arbrepere_id,
         aca_enreg_id enreg_id,
         aca_rang rang,
         aca_branche_typ branche_typ
    FROM se_formule_V) ;

  DELETE FROM &erp.for_arbre aa WHERE EXISTS (SELECT 1 FROM se_for_arbre a  WHERE aa.arbre_id=a.arbre_id) ;
  INSERT INTO &erp.for_arbre (SELECT * FROM se_for_arbre) ;



 DELETE FROM se_for_identifiant ;

  INSERT INTO se_for_identifiant
 (SELECT DISTINCT il_arbre_id arbre_id,
         il_fortype_cod fortype_cod,
         il_enreg_id enreg_id,
         il_nivid_num nivid_num,
         il_formule_cod formule_cod,
         il_reutilisation_cod reutilisation_cod,
         il_utilisateur utilisateur,
         'C' /*il_etat_typ*/ etat_typ,
         NULL /*il_ctrlappels_num*/ ctrlappels_num,
         NULL /*il_ctrlappelsdev_num*/ ctrlappelsdev_num,
         'C' /*il_dev_typ*/ dev_typ,
         il_actdif_ok actdif_ok,
         il_id1_cod id1_cod,
         il_id2_cod id2_cod,
         il_id3_cod id3_cod,
         il_id4_cod id4_cod,
         il_id5_cod id5_cod,
         il_id6_cod id6_cod,
         il_id7_cod id7_cod,
         il_id8_cod id8_cod,
         il_horsse_ok horsse_ok
    FROM se_formule_V) ;

 --delete FROM se_for_identifiant i WHERE exists (select 1 from &erp.for_identifiant ii where ii.arbre_id=i.arbre_id) ;

 CALL uvse_synchroverserp('arbre_id','FOR_IDENTIFIANT','O') ;

  UPDATE &erp.for_identifiant ii SET etat_typ='C',dev_typ='C' WHERE EXISTS
  (SELECT DISTINCT i.id1_cod,i.id2_cod,i.id3_cod,i.id4_cod,i.id5_cod FROM &erp.for_identifiant i,se_formule_v f
  WHERE NOT EXISTS (SELECT 1 FROM &erp.for_action a WHERE a.enreg_id=f.a_enreg_id)
    AND (i.enreg_id=f.il_enreg_id OR i.enreg_id=f.il_enreg_id)
    AND i.enreg_id=ii.enreg_id) ;



 DELETE FROM se_for_condition;
 INSERT INTO se_for_condition
 (SELECT DISTINCT c_arbre_id arbre_id,
         c_enreg_id enreg_id,
         c_forappel_cod forappel_cod,
         c_condition_typ condition_typ,
         c_condition_cod condition_cod,
         c_condition_lib condition_lib,
         c_condition_desc condition_desc,
         c_faux_ok faux_ok,
         c_renvoi_cod renvoi_cod
    FROM se_formule_V) ;

 CALL uvse_synchroverserp('arbre_id','for_condition','O') ;



 DELETE FROM se_for_action;

  INSERT INTO se_for_action
 (SELECT DISTINCT a_arbre_id arbre_id,
         a_enreg_id enreg_id,
         a_fortype_cod fortype_cod,
         a_variable_cod variable_cod,
         a_entite_cod entite_cod,
         a_forappel_cod forappel_cod,
         a_formatsais_cod formatsais_cod,
         a_action_typ action_typ,
         a_action_lib action_lib,
         a_action_desc action_desc,
         a_varinterm_cod varinterm_cod
    FROM se_formule_v) ;

 CALL uvse_synchroverserp('arbre_id','for_action','O') ;



 COMMIT ;



SELECT  &outils.stragg(source) SOURCES,
    AEL_ARBRE_ID, AEL_ARBREPERE_ID, AEL_ENREG_ID, AEL_RANG, AEL_BRANCHE_TYP, IL_ARBRE_ID, IL_FORTYPE_COD, IL_ENREG_ID, IL_NIVID_NUM, IL_FORMULE_COD, IL_REUTILISATION_COD, IL_UTILISATEUR, /*IL_ETAT_TYP, IL_CTRLAPPELS_NUM, IL_CTRLAPPELSDEV_NUM, IL_DEV_TYP,*/ IL_ACTDIF_OK, IL_ID1_COD, IL_ID2_COD, IL_ID3_COD, IL_ID4_COD, IL_ID5_COD, IL_ID6_COD, IL_ID7_COD, IL_ID8_COD, IL_HORSSE_OK, ALC_ARBRE_ID, ALC_ARBREPERE_ID, ALC_ENREG_ID, ALC_RANG, ALC_BRANCHE_TYP, C_ARBRE_ID, C_ENREG_ID, C_FORAPPEL_COD, C_CONDITION_TYP, C_CONDITION_COD, C_CONDITION_LIB, C_CONDITION_DESC, &outils.stragg(C_CONDITION_UV) C_CONDITION_UV, C_FAUX_OK, C_RENVOI_COD, ACA_ARBRE_ID, ACA_ARBREPERE_ID, ACA_ENREG_ID, ACA_RANG, ACA_BRANCHE_TYP, A_ARBRE_ID, A_ENREG_ID, A_FORTYPE_COD, A_VARIABLE_COD, A_ENTITE_COD, A_FORAPPEL_COD, A_FORMATSAIS_COD, A_ACTION_TYP, A_ACTION_LIB, A_ACTION_DESC, &outils.stragg(A_ACTION_UV) a_action_uv, A_VARINTERM_COD
FROM
    (SELECT 'uv' source,ael_arbre_id, ael_arbrepere_id, ael_enreg_id, ael_rang, ael_branche_typ,
      il_arbre_id, il_fortype_cod, il_enreg_id, il_nivid_num, il_formule_cod, il_reutilisation_cod, il_utilisateur, il_etat_typ, il_ctrlappels_num, il_ctrlappelsdev_num, il_dev_typ, il_actdif_ok, il_id1_cod, il_id2_cod, il_id3_cod, il_id4_cod, il_id5_cod, il_id6_cod, il_id7_cod, il_id8_cod, il_horsse_ok,
      alc_arbre_id, alc_arbrepere_id, alc_enreg_id, alc_rang, alc_branche_typ,
      c_arbre_id, c_enreg_id, c_forappel_cod, c_condition_typ, c_condition_cod, c_condition_lib, c_condition_desc, c_condition_uv, c_faux_ok, c_renvoi_cod,
      aca_arbre_id, aca_arbrepere_id, aca_enreg_id, aca_rang, aca_branche_typ,
      a_arbre_id, a_enreg_id, a_fortype_cod, a_variable_cod, a_entite_cod, a_forappel_cod, a_formatsais_cod, a_action_typ, a_action_lib, a_action_desc, a_action_uv, a_varinterm_cod

    FROM se_formule_V f
    UNION all
    SELECT 'se' source,ael.arbre_id, ael.arbrepere_id, ael.enreg_id, ael.rang, ael.branche_typ,
      il.arbre_id, il.fortype_cod, il.enreg_id, il.nivid_num, il.formule_cod, il.reutilisation_cod, il.utilisateur, il.etat_typ, il.ctrlappels_num, il.ctrlappelsdev_num, il.dev_typ, il.actdif_ok, il.id1_cod, il.id2_cod, il.id3_cod, il.id4_cod, il.id5_cod, il.id6_cod, il.id7_cod, il.id8_cod,il.horsse_ok,
      alc.arbre_id, alc.arbrepere_id, alc.enreg_id, alc.rang, alc.branche_typ,
      c.arbre_id, c.enreg_id, c.forappel_cod, c.condition_typ, c.condition_cod, c.condition_lib, c.condition_desc, NULL condition_uv, c.faux_ok, c.renvoi_cod,
      aca.arbre_id, aca.arbrepere_id, aca.enreg_id, aca.rang, aca.branche_typ,
      a.arbre_id, a.enreg_id, a.fortype_cod, a.variable_cod, a.entite_cod, a.forappel_cod, a.formatsais_cod, a.action_typ, a.action_lib, a.action_desc, NULL action_uv, a.varinterm_cod
    FROM &erp.for_arbre ael ,&erp.for_identifiant il ,&erp.for_arbre alc, &erp.for_condition c, &erp.for_arbre aca, &erp.for_action a
    WHERE ael.arbre_id=alc.arbrepere_id
      AND alc.arbre_id=aca.arbrepere_id
      AND il.arbre_id=ael.arbre_id
      AND c.arbre_id=alc.arbre_id
      AND a.arbre_id=aca.arbre_id
      AND il.fortype_cod='ATTVRT')
GROUP BY AEL_ARBRE_ID, AEL_ARBREPERE_ID, AEL_ENREG_ID, AEL_RANG, AEL_BRANCHE_TYP, IL_ARBRE_ID, IL_FORTYPE_COD, IL_ENREG_ID, IL_NIVID_NUM, IL_FORMULE_COD, IL_REUTILISATION_COD, IL_UTILISATEUR, /*IL_ETAT_TYP, IL_CTRLAPPELS_NUM, IL_CTRLAPPELSDEV_NUM, IL_DEV_TYP,*/ IL_ACTDIF_OK, IL_ID1_COD, IL_ID2_COD, IL_ID3_COD, IL_ID4_COD, IL_ID5_COD, IL_ID6_COD, IL_ID7_COD, IL_ID8_COD, IL_HORSSE_OK, ALC_ARBRE_ID, ALC_ARBREPERE_ID, ALC_ENREG_ID, ALC_RANG, ALC_BRANCHE_TYP, C_ARBRE_ID, C_ENREG_ID, C_FORAPPEL_COD, C_CONDITION_TYP, C_CONDITION_COD, C_CONDITION_LIB, C_CONDITION_DESC, /*C_CONDITION_UV,*/ C_FAUX_OK, C_RENVOI_COD, ACA_ARBRE_ID, ACA_ARBREPERE_ID, ACA_ENREG_ID, ACA_RANG, ACA_BRANCHE_TYP, A_ARBRE_ID, A_ENREG_ID, A_FORTYPE_COD, A_VARIABLE_COD, A_ENTITE_COD, A_FORAPPEL_COD, A_FORMATSAIS_COD, A_ACTION_TYP, A_ACTION_LIB, A_ACTION_DESC,/* A_ACTION_UV,*/ A_VARINTERM_COD
HAVING Count(*) !=2
ORDER BY il_id1_cod, il_id2_cod, il_id3_cod, il_id4_cod, il_id5_cod, il_id6_cod,C_CONDITION_DESC,alc_rang,A_ACTION_DESC,aca_rang  ;

